package com.bot.rf.sql.util;

import com.bot.rf.entity.BookInfo;
import com.bot.rf.entity.BookLocation;
import com.bot.rf.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import static com.bot.rf.util.MyLogger.LOGGER;
import static com.bot.rf.util.MyLogger.getTrace;
import static com.bot.rf.config.UserConfig.*;
/**
 * @program: report-form-demo
 * @description: 用户oracle数据库
 * @author: zyy
 * @create: 2021-02-26 16:58
 **/
public class OracleSqlUtil {

    /**
     * 数据库连接、查询、结果集
     */
    private static Connection conn = null;
    private static Statement stmt = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet resultSet = null;

    //--------------------------- 远程oracle配置 start -----------------------------
    /**
     * url格式  jdbc:oracle:thin:@host:port:SID
     */
    private static String URL = "jdbc:oracle:thin:@" +
                          USER_DATABASE_HOST + ":" + USER_DATABASE_PORT + ":" + USER_DATABASE_SID;
    /**
     * 表名
     */
    private static String TABLE_NAME = USER_MAIN_TABLE_NAME;

    /**
     * 账号和密码
     */
    private static String USER_NAME = USER_DATABASE_USERNAME;
    private static String PASSWORD = USER_DATABASE_PASSWORD;
    //--------------------------- 远程oracle配置  end  -----------------------------

    /**
     * 本地配置
     */
    //private static String URL = "jdbc:oracle:thin:@localhost:1521:orcw";
    //private static String TABLE_NAME = "RFID.M_TRANSFORM_TAG";

    //--------------------------- 配置方法 start -------------------------------
    /**
     * 创建连接
     *
     * @return
     */
    public static boolean createOrConnect() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            LOGGER.info("连接成功...");
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            LOGGER.info("URL：" + URL);
            return false;
        }
        return true;
    }

    /**
     * 关闭自动提交
     * @return
     */
    public static boolean closeAutoCommit() {
        try {
            if (conn == null || conn.isClosed()) {
                createOrConnect();
            }
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 提交
     * @return
     */
    public static boolean commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 创建 Statement
     * @return
     */
    public static boolean createStatement() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                return true;
            }
            System.out.println("Sql Statement关闭，重新连接...");
            stmt = conn.createStatement();
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
            return false;
        }
        return true;
    }

    /**
     * 关闭连接
     */
    public static void closeConnect() {
        try {
            if (stmt != null && !stmt.isClosed()) {
                stmt.close();
            }
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }
    //--------------------------- 配置方法  end  -------------------------------

    //--------------------------- 自定义方法 start -----------------------------
    /**
     * 1、查询全部
     *
     * @return
     */
    public static List<BookInfo> queryBookList() {
        createOrConnect();
        createStatement();
        // 自定义sql
        String sql = "select TAG_ID as tagId, BOOK_ID as barcode, BOOK_NAME as book_name, BOOK_INDEX as book_index," +
                     " AUTHOR as author, BOOK_PLACE as book_place from " + TABLE_NAME;
        List<BookInfo> resultList = new ArrayList<>();
        try {
            resultSet = stmt.executeQuery(sql);
            while (resultSet.next()) {
                BookInfo bookInfo = new BookInfo();
                String tagId = resultSet.getString("tagId");
                String bookName = resultSet.getString("book_name");
                String bookIndex = resultSet.getString("book_index");
                //String caseNo = resultSet.getString("book_place");
                String barcode = resultSet.getString("barcode");
                bookInfo.setTagId(tagId);
                bookInfo.setBarcode(barcode);
                bookInfo.setBookName(bookName);
                bookInfo.setBookIndex(bookIndex);
                // 层架标临时存在areaNo里
                //BookLocation location = new BookLocation(2);
                //location.areaNo = caseNo;
                //bookInfo.rightLocation = location;
                resultList.add(bookInfo);
            }
            resultSet.close();
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
        }
        return resultList;
    }

    /**
     * 根据 tagId 查询图书信息
     *
     * @param tagId
     * @return
     */
    public static BookInfo queryBook(String tagId) {
        createOrConnect();
        createStatement();
        // 自定义sql
        String sql = "select BOOK_ID as barcode, BOOK_NAME as book_name, BOOK_INDEX as book_index, AUTHOR as author, " +
                "BOOK_PLACE as book_place from " + TABLE_NAME + " where TAG_ID = '" + tagId + "' ";
        BookInfo bookInfo = new BookInfo();
        bookInfo.setTagId(tagId);
        try {
            resultSet = stmt.executeQuery(sql);
            if (resultSet.next()) {
                String bookName = resultSet.getString("book_name");
                String bookIndex = resultSet.getString("book_index");
                String caseNo = resultSet.getString("book_place");
                String barcode = resultSet.getString("barcode");
                bookInfo.setBarcode(barcode);
                bookInfo.setBookName(bookName);
                bookInfo.setBookIndex(bookIndex);
                // 层架标临时存在areaNo里
                BookLocation location = new BookLocation(2);
                location.setAreaNo(caseNo);
                bookInfo.setRightLocation(location);
            }
            resultSet.close();
        } catch (Exception e) {
            LOGGER.error(getTrace(e));
            return null;
        }
        return bookInfo;
    }

    /**
     * 批量更新
     * 考虑到性能设置每次 最大batch数量 50000
     *
     * @param bookInfos
     */
    public static void batchUpdateBookInfo(List<BookInfo> bookInfos) {
        // 关闭自动提交事务
        closeAutoCommit();
        List<List<BookInfo>> res = StringUtil.splitList(bookInfos,50000);
        for (List<BookInfo> l:res) {
            try {
                Thread.sleep(300);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            updateBookInfos(l);
        }
        // 手动提交事务
        commit();
    }

    /**
     * 更新图书层架标
     * @param bookInfos
     */
    public static void updateBookInfos(List<BookInfo> bookInfos) {
        try {
            if (conn == null || conn.isClosed()) {
                LOGGER.warn("数据库连接断开，重新连接...");
                createOrConnect();
            }
        } catch (SQLException e) {
            LOGGER.error(getTrace(e));
        }
        String sql = "UPDATE " + TABLE_NAME + " SET BOOK_PLACE=? WHERE TAG_ID=? ";
        try {
            pstmt = conn.prepareStatement(sql);
            for (BookInfo b : bookInfos) {
                pstmt.setString(1, b.getLocation().getAreaNo());
                pstmt.setString(2, b.getTagId());
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量插入（本地oracle测试使用）
     * @param bookInfoList
     */
    public static void batchInsertBookInfo(List<BookInfo> bookInfoList) {
        // 关闭自动提交事务
        closeAutoCommit();
        for (BookInfo bookInfo : bookInfoList) {
            insertBookInfo(bookInfo);
        }
        // 手动提交事务
        commit();
    }

    /**
     * 插入数据（本地oracle测试使用）
     * @param bookInfo
     */
    public static void insertBookInfo(BookInfo bookInfo) {
        try {
            if (conn == null || conn.isClosed()) {
                createOrConnect();
            }
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
        String sql = "replace INTO RFID.M_TRANSFORM_TAG(TAG_ID, BOOK_ID, BOOK_NAME, BOOK_INDEX)" +
                " VALUES(?,?,?,?);";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, bookInfo.getTagId());
            pstmt.setString(2, bookInfo.getBarcode());
            pstmt.setString(3, bookInfo.getBookName());
            pstmt.setString(4, bookInfo.getBookIndex());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            LOGGER.error(e.getMessage());
        }
    }
    //--------------------------- 自定义方法  end  -----------------------------
}
